#ifndef XG_SQLITECONNECT_CPP
#define XG_SQLITECONNECT_CPP
//////////////////////////////////////////////////////////////
#include "../../stdx/cmd.h"
#include "../SQLiteConnect.h"

#ifndef XG_SQLITE_NOTRANSACTION_MSG
#define XG_SQLITE_NOTRANSACTION_MSG		"no transaction"
#endif

static string GetErrorString(sqlite3* conn)
{
	const char* msg = sqlite3_errmsg(conn);

	return msg ? msg : stdx::EmptyString();
}

void SQLiteRowData::close()
{
	stmt = NULL;
}
bool SQLiteRowData::isNull()
{
	return data == NULL;
}
SQLiteRowData::~SQLiteRowData()
{
	close();
}
SQLiteRowData::SQLiteRowData(sqlite3_stmt* stmt)
{
	this->data = NULL;
	this->stmt = stmt;
}
const char* SQLiteRowData::getData(int index)
{
	return data = (const char*)(sqlite3_column_text(stmt, index));
}
int SQLiteRowData::getData(int index, char* data, int len)
{
	const char* tmp = getData(index);

	if (isNull()) return 0;

	int sz = getDataLength(index);
	
	if (sz <= 0) return sz;
	if (len > sz) len = sz;

	memcpy(data, tmp, len);

	return len;
}
int SQLiteRowData::getDataLength(int index)
{
	if (getData(index) == NULL) return 0;

	return sqlite3_column_bytes(stmt, index);
}
string SQLiteRowData::getString(int index)
{
	const char* data = getData(index);
	
	return data ? data : stdx::EmptyString();
}

SQLiteQueryResult::SQLiteQueryResult(sqlite3* conn, sqlite3_stmt* stmt)
{
	this->res = 0;
	this->conn = conn;
	this->stmt = stmt;
}
SQLiteQueryResult::~SQLiteQueryResult()
{
	close();
}
void SQLiteQueryResult::close()
{
	if (stmt)
	{
		sqlite3_finalize(stmt);
		stmt = NULL;
	}

	conn = NULL;
}
int SQLiteQueryResult::rows()
{
	return sqlite3_data_count(stmt) > 0 ? 1 : 0;
}
int SQLiteQueryResult::cols()
{
	return sqlite3_column_count(stmt);
}
string SQLiteQueryResult::getColumnName(int index)
{
	return sqlite3_column_name(stmt, index);
}
bool SQLiteQueryResult::getColumnData(ColumnData& data, int index)
{
	const char* ptr = sqlite3_column_decltype(stmt, index);

	if (ptr == NULL) return false;

	data.size = 0;
	data.scale = 0;
	data.nullable = true;
	data.type = sqlite3_column_type(stmt, index);

	strncpy(data.name, getColumnName(index).c_str(), sizeof(data.name));
	data.name[sizeof(data.name) - 1] = 0;

	string tmp = ptr;

	stdx::tolower(tmp);
	ptr = tmp.c_str();

	if (strstr(ptr, "int"))
	{
		data.type = DBData_Integer;
	}
	else if (strstr(ptr, "char"))
	{
		data.type = DBData_String;
	}
	else if (strstr(ptr, "blob") || strstr(ptr, "text"))
	{
		data.type = DBData_Blob;
	}
	else if (strstr(ptr, "date") || strstr(ptr, "timestamp"))
	{
		data.size = 14;
		data.type = DBData_DateTime;
	}
	else if (strstr(ptr, "float") || strstr(ptr, "real") || strstr(ptr, "double") || strstr(ptr, "number") || strstr(ptr, "numeric"))
	{
		data.type = DBData_Float;
	}
	else
	{
		data.type = DBData_Other;
	}
	
	if (data.size == 0)
	{
		const char* p = strchr(ptr, '(');

		if (p && *(++p))
		{
			p = SkipStartString(p, " \r\n\t");
			
			if ((data.size = stdx::atoi(p)) < 0) data.size = 0;
		}
	}
	
	return true;
}
sp<RowData> SQLiteQueryResult::next()
{
	sp<SQLiteRowData> row;

	if ((res = sqlite3_step(stmt)) == SQLITE_ROW) row = newsp<SQLiteRowData>(stmt);

	return row;
}
bool SQLiteQueryResult::seek(int ofs)
{
	return false;
}
int SQLiteQueryResult::getErrorCode()
{
	return res = sqlite3_errcode(conn);
}
string SQLiteQueryResult::getErrorString()
{
	return GetErrorString(conn);
}

SQLiteConnect::SQLiteConnect()
{
	this->res = 0;
	this->conn = NULL;
}
SQLiteConnect::~SQLiteConnect()
{
	close();
}
const char* SQLiteConnect::getSystemName()
{
	return "SQLite";
}
bool SQLiteConnect::commit()
{
	char* msg = NULL;

	res = sqlite3_exec(conn, "commit transaction", NULL, NULL, &msg);
	
	if (msg == NULL)
	{
		errmsg.clear();
	}
	else
	{
		errmsg = msg;
		sqlite3_free(msg);
	}

	return res == SQLITE_OK;
}
bool SQLiteConnect::rollback()
{
	char* msg = NULL;

	res = sqlite3_exec(conn, "rollback transaction", NULL, NULL, &msg);

	if (msg == NULL)
	{
		errmsg.clear();
	}
	else
	{
		errmsg = msg;
		sqlite3_free(msg);
	}

	return res == SQLITE_OK;
}
bool SQLiteConnect::connect(const string& filename)
{
	close();

	res = sqlite3_open(filename.c_str(), &conn);

	CHECK_FALSE_RETURN(conn);

	if (res == SQLITE_OK)
	{
		res = sqlite3_extended_result_codes(conn, 1); 
		
		if (res == SQLITE_OK)
		{
			sqlite3_busy_timeout(conn, 5000);
			execute("PRAGMA synchronous=OFF");

			filepath = filename;

			return true;
		}
	}

	sqlite3_close_v2(conn);
	conn = NULL;
	
	return false;
}
bool SQLiteConnect::connect(const string& host, int port, const string& name, const string& usr, const string& passwd)
{
	return connect(name);
}
void SQLiteConnect::close()
{
	if (conn)
	{
		sqlite3_close_v2(conn);
		conn = NULL;
	}
}
int SQLiteConnect::getPrimaryKeys(vector<string>& vec, const string& tabname)
{
	string sql = "SELECT sql FROM sqlite_master WHERE type='table' AND name='" + tabname + "'";

	sp<QueryResult> rs = query(sql);
	
	if (!rs) return -1;

	sp<RowData> row = rs->next();

	if (!row) return -1;

	vec.clear();

	sql = stdx::replace(row->getString(0), "primary key", "PRIMARY KEY");

	string name;
	const char* p = NULL;
	const char* q = NULL;
	const char* str = sql.c_str();

	if ((p = strstr(str, "PRIMARY KEY")))
	{
		
		if (*(p = SkipStartString(p + strlen("PRIMARY KEY"), " \r\n\t")) == '(')
		{
			++p;
			
			if ((q = strchr(p, ')')))
			{
				name = stdx::replace(string(p, q), " ASC", " ");
				name = stdx::replace(name, " DESC", " ");
				name = stdx::replace(name, " desc", " ");
				name = stdx::replace(name, " asc", " ");
				stdx::split(vec, name, ",");

				for (string& item : vec) item = stdx::trim(item, " \'\"\r\n\t");

				return vec.size();
			}
		}
	}

	if (vec.empty())
	{
		sql = "SELECT * FROM " + tabname + " WHERE 1=0";

		if (!(rs = query(sql))) return -1;

		int cols = rs->cols();

		for (int i = 0; i < cols; i++)
		{
			if ((name = rs->getColumnName(i)).empty()) return -1;

			vec.push_back(name);
		}
	}

	return vec.size();
}
int SQLiteConnect::getTables(vector<string>& vec)
{
	const char* sql = "SELECT name FROM sqlite_master WHERE type='table'";

	sp<QueryResult> rs = query(sql);

	vec.clear();
	
	if (rs)
	{
		sp<RowData> row;

		while (row = rs->next()) vec.push_back(row->getString(0));
	}

	return vec.size();
}
sp<QueryResult> SQLiteConnect::query(const string& sqlcmd)
{
	static vector<DBData*> vec;

	return query(sqlcmd.c_str(), vec);
}
sp<QueryResult> SQLiteConnect::query(const string& sqlcmd, const vector<DBData*>& vec)
{
	sqlite3_stmt* stmt = NULL;

	res = sqlite3_prepare(conn, sqlcmd.c_str(), sqlcmd.length(), &stmt, NULL);
	
	if (res == SQLITE_OK && bind(stmt, vec) == SQLITE_OK)
	{
		sp<QueryResult> rs = newsp<SQLiteQueryResult>(conn, stmt);
		
		updateStatus(rs->rows(), sqlcmd, vec);

		return rs;
	}

	errmsg = GetErrorString(conn);
	
	updateStatus(SQLRtn_Error, sqlcmd, vec);

	if (stmt) sqlite3_finalize(stmt);

	return NULL;
}
int SQLiteConnect::execute(const string& sqlcmd)
{
	char* msg = NULL;

	res = sqlite3_exec(conn, sqlcmd.c_str(), NULL, NULL, &msg);
	
	if (msg == NULL)
	{
		errmsg.clear();
	}
	else
	{
		errmsg = msg;

		sqlite3_free(msg);
	}

	if (res == SQLITE_OK) return updateStatus(sqlite3_changes(conn), sqlcmd);

	if (errmsg.empty()) errmsg = GetErrorString(conn);

	updateStatus(SQLRtn_Error, sqlcmd);

	if (res == SQLITE_CONSTRAINT) return SQLRtn_Duplicate;

	if (strstr(errmsg.c_str(), "UNIQUE constraint failed")) return SQLRtn_Duplicate;

	return SQLRtn_Error;
}
int SQLiteConnect::bind(void* stmt, const vector<DBData*>& vec)
{
	bindvec.clear();

	sqlite3_stmt* stm = (sqlite3_stmt*)(stmt);

	for (size_t i = 0; i < vec.size(); i++)
	{
		DBData* item = vec[i];
		E_DBDATA_TYPE type = item->type();

		if (item->isNull())
		{
			res = sqlite3_bind_null(stm, i + 1);
		}
		else if (type == DBData_String)
		{
			DBString* data = (DBString*)(item);

			res = sqlite3_bind_text(stm, i + 1, data->val().c_str(), data->val().length(), NULL);
		}
		else if (type == DBData_Integer)
		{
			DBInteger* data = (DBInteger*)(item);

			res = sqlite3_bind_int64(stm, i + 1, data->val());
		}
		else if (type == DBData_Float)
		{
			DBFloat* data = (DBFloat*)(item);

			res = sqlite3_bind_double(stm, i + 1, data->val());
		}
		else if (type == DBData_Blob)
		{
			DBBlob* data = (DBBlob*)(item);

			res = sqlite3_bind_blob(stm, i + 1, data->val().str(), data->size(), NULL);
		}
		else
		{
			bindvec.push_back(item->toString());

			const SmartBuffer& val = bindvec.back();

			res = sqlite3_bind_text(stm, i + 1, val.str(), val.size(), NULL);
		}

		if (res == SQLITE_OK) continue;

		errmsg = GetErrorString(conn);

		sqlite3_finalize(stm);

		return SQLRtn_Error;
	}

	return SQLITE_OK;
}
int SQLiteConnect::execute(const string& sqlcmd, const vector<DBData*>& vec)
{
	char* msg = NULL;
	sqlite3_stmt* stmt = NULL;

	res = sqlite3_prepare(conn, sqlcmd.c_str(), sqlcmd.length(), &stmt, NULL);
	
	if (res == SQLITE_OK && bind(stmt, vec) == SQLITE_OK)
	{
		res = sqlite3_step(stmt);
		
		if (res == SQLITE_OK || res == SQLITE_DONE)
		{
			res == SQLITE_OK;

			sqlite3_finalize(stmt);

			return updateStatus(sqlite3_changes(conn), sqlcmd, vec);
		}

		errmsg = GetErrorString(conn);

		updateStatus(SQLRtn_Error, sqlcmd, vec);
	
		sqlite3_finalize(stmt);

		return res == SQLITE_CONSTRAINT ? SQLRtn_Duplicate : SQLRtn_Error;
	}

	errmsg = GetErrorString(conn);
	
	updateStatus(SQLRtn_Error, sqlcmd, vec);

	if (stmt) sqlite3_finalize(stmt);
		
	return SQLRtn_Error;
}
int SQLiteConnect::getErrorCode()
{
	return res;
}
string SQLiteConnect::getErrorString()
{
	return errmsg;
}

bool SQLiteConnect::Setup()
{
	sqlite3_config(SQLITE_CONFIG_MULTITHREAD);

	return true;
}
const char* SQLiteConnect::GetShellExePath()
{
	static string exepath;

	if (exepath.length() > 0) return exepath.c_str();

#ifdef XG_LINUX
	exepath = stdx::translate("$PRODUCT_HOME/bin/sqlite");
#else
	exepath = stdx::translate("$PRODUCT_HOME/bin/sqlite.exe");
#endif

	if (path::type(exepath) < eFILE) exepath = "sqlite";

	return exepath.c_str();
}
bool SQLiteConnect::Check(const string& filename)
{
	SmartBuffer data = proc::exec(GetShellExePath(), filename, "pragma quick_check");

	return data.str() && stdx::toupper(stdx::trim(data.str())) == "OK";
}
bool SQLiteConnect::Modify(const string& filename)
{
	long len = path::size(filename);

	CHECK_FALSE_RETURN(len >= 1024 && len <= XG_MEMFILE_MAXSZ);

	vector<string> vec;
	SQLiteConnect sqlite;
	SmartBuffer buffer(len + len);

	CHECK_FALSE_RETURN(sqlite.connect(filename));
	CHECK_FALSE_RETURN((len = sqlite.getTables(vec)) > 0);

	string tmpname = filename + ".tmp";
	string bakname = filename + ".bak";
	string sqlname = filename + ".sql";
	string command = stdx::format("%s %s .dump", stdx::quote(GetShellExePath()).c_str(), stdx::quote(filename).c_str());

	CHECK_FALSE_RETURN(cmdx::RunCommand(command, buffer.str(), buffer.size()) > 0);

	string msg = stdx::trim(buffer.str());
	size_t pos = msg.rfind('\n');

	if (pos == string::npos) return false;

	string line = msg.substr(pos + 1);

	if (stdx::toupper(line).find("ROLLBACK;") == 0) msg = msg.substr(0, pos + 1) + "COMMIT;";

	XFile file;

	CHECK_FALSE_RETURN(file.create(sqlname) && file.write(msg.c_str(), msg.length()) > 0);

	file.close();

	path::remove(tmpname);

	command = stdx::format("%s %s < %s && echo success", stdx::quote(GetShellExePath()).c_str(), stdx::quote(tmpname).c_str(), stdx::quote(sqlname).c_str());

	CHECK_FALSE_RETURN(cmdx::RunCommand(command, buffer.str(), buffer.size()) > 0);
	CHECK_FALSE_RETURN(strstr(buffer.str(), "success"));

	path::remove(sqlname);

	CHECK_FALSE_RETURN(sqlite.connect(tmpname));
	CHECK_FALSE_RETURN(len == sqlite.getTables(vec));

	sqlite.close();

	path::remove(bakname);

	CHECK_FALSE_RETURN(path::rename(filename, bakname));
	CHECK_FALSE_RETURN(path::rename(tmpname, filename));

	return true;
}
bool SQLiteConnect::Backup(const string& filename)
{
	string path = path::parent(filename) + "/";
	string back = path + "." + path::name(filename);

	CHECK_FALSE_RETURN(path::copy(filename, back));

	SetPathAttributes(back.c_str(), eHIDDEN);

	return true;
}
bool SQLiteConnect::Restore(const string& filename)
{
	string path = path::parent(filename) + "/";
	string back = path + "." + path::name(filename);

	return path::copy(back, filename);
}
//////////////////////////////////////////////////////////////
#endif